package dao;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import bo.Estoque;
import exception.DataException;
import exception.DatabaseConnectException;
import exception.EmptyResultException;

public class EstoqueDAO {

	public ArrayList<Estoque> consultaTodosRegistros()
			throws EmptyResultException, SQLException, DataException,
			DatabaseConnectException {

		ArrayList<Estoque> estoques = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = Conexao
					.getInst()
					.getConnection()
					.prepareStatement(
							"SELECT estoque.id, estoque.ds_estoque, estoque.dt_atualizacao FROM estoque"
									+ " ORDER BY estoque.id;");

			resultSet = preparedStatement.executeQuery();

			estoques = new ArrayList<Estoque>();

			while (resultSet.next()) {
				Estoque estoque = new Estoque();
				estoque.setId(resultSet.getInt(1));
				estoque.setDescricao(resultSet.getString(2));
				estoque.setDt_atualizacao(resultSet.getDate(3));
				estoques.add(estoque);
			}

			if (estoques.isEmpty()) {
				throw new EmptyResultException();
			}

		} catch (SQLException e) {
			throw new DataException(e, DataException.SELECT);

		} catch (DatabaseConnectException e) {
			throw new DatabaseConnectException(e,
					DatabaseConnectException.CONNECT);
		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return estoques;

	}

	public ArrayList<Estoque> consultaPorId(Integer id)
			throws DatabaseConnectException, SQLException,
			EmptyResultException, DataException {

		ArrayList<Estoque> estoques = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = Conexao
					.getInst()
					.getConnection()
					.prepareStatement(
							"SELECT estoque.id, estoque.ds_estoque, estoque.dt_atualizacao FROM estoque"
									+ " WHERE estoque.id = ? ORDER BY estoque.id;");

			preparedStatement.setInt(1, id);

			resultSet = preparedStatement.executeQuery();

			estoques = new ArrayList<Estoque>();

			while (resultSet.next()) {
				Estoque estoque = new Estoque();
				estoque.setId(resultSet.getInt(1));
				estoque.setDescricao(resultSet.getString(2));
				estoque.setDt_atualizacao(resultSet.getDate(3));
				estoques.add(estoque);
			}

			if (estoques.isEmpty()) {
				throw new EmptyResultException();
			}
		} catch (SQLException e) {
			throw new DataException(e, DataException.SELECT);

		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return estoques;

	}

	public ArrayList<Estoque> consultaPorDescricao(String descricao)
			throws DatabaseConnectException, DataException, SQLException,
			EmptyResultException {

		ArrayList<Estoque> estoques = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			preparedStatement = Conexao
					.getInst()
					.getConnection()
					.prepareStatement(
							"SELECT id, ds_estoque, dt_atualizacao FROM estoque "
									+ "WHERE estoque.ds_estoque LIKE ? ORDER BY ds_estoque");

			preparedStatement.setString(1, "%"+descricao+"%");
			
			resultSet = preparedStatement.executeQuery();

			estoques = new ArrayList<Estoque>();

			while (resultSet.next()) {
				Estoque estoque = new Estoque();
				estoque.setId(resultSet.getInt(1));
				estoque.setDescricao(resultSet.getString(2));
				estoque.setDt_atualizacao(resultSet.getDate(3));
				estoques.add(estoque);
			}

			if (estoques.isEmpty()) {
				throw new EmptyResultException();
			}

		} catch (SQLException e) {
			throw new DataException(e, DataException.SELECT);

		} finally {
			if (resultSet != null) {
				resultSet.close();
			}
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}

		return estoques;
	}

	public void incluir(Estoque estoque) throws DatabaseConnectException,
			DataException, SQLException {

		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = Conexao
					.getInst()
					.getConnection()
					.prepareStatement(
							"INSERT INTO estoque (id, ds_estoque, dt_atualizacao)"
									+ " VALUES (nextval('seqestoque'), ?, ?);");

			preparedStatement.setString(1, estoque.getDescricao());
			if (estoque.getDtAtualizacao() != null) {
				preparedStatement.setDate(2, new Date(estoque
						.getDtAtualizacao().getTime()));
			} else {
				preparedStatement.setNull(2, Types.DATE);
			}

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			throw new DataException(e, DataException.INSERT);

		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}

	public void excluir(Integer codigo) throws DatabaseConnectException,
			DataException, SQLException {

		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = Conexao
					.getInst()
					.getConnection()
					.prepareStatement(
							"DELETE FROM estoque WHERE estoque.id = ?;");

			preparedStatement.setInt(1, codigo);

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			throw new DataException(e, DataException.DELETE);

		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}

	public void alterar(Estoque estoque) throws SQLException,
			DatabaseConnectException, DataException {

		PreparedStatement preparedStatement = null;

		try {
			preparedStatement = Conexao
					.getInst()
					.getConnection()
					.prepareStatement(
							"UPDATE estoque "
									+ "SET ds_estoque = ?, dt_atualizacao = ?"
									+ " WHERE estoque.id = ?;");

			preparedStatement.setString(1, estoque.getDescricao());

			if (estoque.getDtAtualizacao() != null) {
				preparedStatement.setDate(2, new Date(estoque
						.getDtAtualizacao().getTime()));
			} else {
				preparedStatement.setNull(2, Types.DATE);

			}

			preparedStatement.setInt(3, estoque.getId());

			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			throw new DataException(e, DataException.UPDATE);

		} finally {
			if (preparedStatement != null) {
				preparedStatement.close();
			}
		}
	}
}
